package com.scalar.chiptrack.finance.reports.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.finance.reports.DirectMargin;
import com.scalar.chiptrack.finance.reports.forms.DirectMarginReportForm;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.ScalarException;


import java.util.ArrayList;
import java.util.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * User: prasad
 * Date: Jun 11, 2007
 * Time: 4:37:36 PM
 */
public class DirectMarginDAO
{

    public static ArrayList loadCustomerDirectMargins( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        ArrayList directMarginList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT CUSTOMER_ID, CUSTOMER_NAME, SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) GROUP BY CUSTOMER_ID, CUSTOMER_NAME " );
            pstmt.setDate( 1, StringUtils.getSQLDate(  new Date() ) );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                directMargin = new DirectMargin();

                directMargin.setCustomerId  ( rset.getInt( "CUSTOMER_ID" ) );
                directMargin.setCustomerName( rset.getString ( "CUSTOMER_NAME" ) );
                directMargin.setUnits       ( rset.getInt( "COOL_QTY" ) );
                directMargin.setUnitsCost( rset.getFloat( "COOL_UNIT_COST" ) );
                directMargin.setUnitsPrice( rset.getFloat( "PRICE" ) );
                directMargin.setDirectMargin( rset.getFloat( "DIFF_COST" ) );
                directMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );

                directMarginList.add( directMargin );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return directMarginList;
    }

    public static ArrayList loadProductDirectMargins( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        ArrayList directMarginList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT PROCESSSTEP_ID, PART_NUMBER, SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) GROUP BY PROCESSSTEP_ID, PART_NUMBER " );
            pstmt.setDate( 1, StringUtils.getSQLDate(  new Date() )  );
            
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                directMargin = new DirectMargin();

                directMargin.setProcessstepId   ( rset.getInt( "PROCESSSTEP_ID" ) );
                directMargin.setPartNumber      ( rset.getString ( "PART_NUMBER" ) );
                directMargin.setActualCost      ( rset.getFloat( "COST_CODE_COST" ) );
                directMargin.setUnits           ( rset.getInt( "COOL_QTY" ) );
                directMargin.setUnitsCost       ( rset.getFloat( "COOL_UNIT_COST" ) );
                directMargin.setUnitsPrice      ( rset.getFloat( "PRICE" ) );
                directMargin.setDirectMargin    ( rset.getFloat( "DIFF_COST" ) );
                directMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );

                directMarginList.add( directMargin );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return directMarginList;
    }


    public static ArrayList loadCostCodeDirectMargins( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        ArrayList directMarginList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT COST_CODE, SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) WHERE COST_CODE IS NOT NULL GROUP BY COST_CODE" );
            pstmt.setDate( 1, StringUtils.getSQLDate(  new Date() )  );
            
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                directMargin = new DirectMargin();

                directMargin.setCostCode    ( rset.getString ( "COST_CODE" ) );
                directMargin.setActualCost  ( rset.getFloat( "COST_CODE_COST" ) );
                directMargin.setUnits       ( rset.getInt( "COOL_QTY" ) );
                directMargin.setUnitsCost   ( rset.getFloat( "COOL_UNIT_COST" ) );
                directMargin.setUnitsPrice  ( rset.getFloat( "PRICE" ) );
                directMargin.setDirectMargin( rset.getFloat( "DIFF_COST" ) );
                directMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );

                directMarginList.add( directMargin );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return directMarginList;
    }


    public static void loadProductDirectMarginsByCustomer( DBTransaction dbTransaction, ArrayList directMarginList, int customerId  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        DirectMargin productDirectMargin;
        ArrayList productDirectMarginList;
        
        int listSize = ( directMarginList == null ) ? 0 : directMarginList.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT CUSTOMER_ID, CUSTOMER_NAME,  PROCESSSTEP_ID, PART_NUMBER , SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) WHERE CUSTOMER_ID = ? GROUP BY CUSTOMER_ID, CUSTOMER_NAME, PROCESSSTEP_ID, PART_NUMBER  " );

            for ( int i = 0; i < listSize; i++ )
            {
                directMargin = ( DirectMargin ) directMarginList.get ( i );
                //System.out.println("                -- customer obj cust - id =  " + directMargin.getCustomerId() + " customerId = " + customerId );
                if ( directMargin != null && directMargin.getCustomerId() == customerId )
                {
                    if ( directMargin.getDirectMarginList() == null || directMargin.getDirectMarginList().isEmpty()  )
                    {
                        productDirectMarginList = new ArrayList();
                        
                        pstmt.setDate( 1, StringUtils.getSQLDate(  new Date() )  );
                        pstmt.setInt ( 2, customerId );
                        rset = pstmt.executeQuery();

                        while ( rset.next() )
                        {
                            productDirectMargin = new DirectMargin();
                            productDirectMargin.setProcessstepId( rset.getInt( "PROCESSSTEP_ID" ) );
                            productDirectMargin.setPartNumber   ( rset.getString ( "PART_NUMBER" ) );
                            productDirectMargin.setUnits        ( rset.getInt( "COOL_QTY" ) );
                            productDirectMargin.setUnitsCost    ( rset.getFloat( "COOL_UNIT_COST" ) );
                            productDirectMargin.setUnitsPrice   ( rset.getFloat( "PRICE" ) );
                            productDirectMargin.setActualCost   ( rset.getFloat( "COST_CODE_COST" ) );
                            productDirectMargin.setDirectMargin ( rset.getFloat( "DIFF_COST" ) );
                            productDirectMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );
                            
                            //System.out.println("                -- productDirectMargin =  " + productDirectMargin.toString() );
                            productDirectMarginList.add( productDirectMargin );
                        }
                        directMargin.setDirectMarginList( productDirectMarginList );
                    }
                    else
                    {
                        directMargin.setDirectMarginList( null );
                    }
                    break;
                }
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        
    }

    public static void loadCustomerDirectMarginsByProduct( DBTransaction dbTransaction, ArrayList directMarginList, int processstepId  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        DirectMargin customerDirectMargin;
        ArrayList customerDirectMarginList;

        int listSize = ( directMarginList == null ) ? 0 : directMarginList.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT CUSTOMER_ID, CUSTOMER_NAME,  PROCESSSTEP_ID, PART_NUMBER , SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) WHERE PROCESSSTEP_ID = ? GROUP BY PROCESSSTEP_ID, PART_NUMBER, CUSTOMER_ID, CUSTOMER_NAME  " );

            for ( int i = 0; i < listSize; i++ )
            {
                directMargin = ( DirectMargin ) directMarginList.get ( i );
                //System.out.println("                -- product obj proc - id =  " + directMargin.getProcessstepId() + " processstepId = " + processstepId );
                if ( directMargin != null && directMargin.getProcessstepId() == processstepId )
                {
                    if ( directMargin.getDirectMarginList() == null || directMargin.getDirectMarginList().isEmpty()  )
                    {
                        customerDirectMarginList = new ArrayList();

                        pstmt.setDate( 1, StringUtils.getSQLDate(  new Date() )  );
                        pstmt.setInt ( 2, processstepId );
                        
                        rset = pstmt.executeQuery();

                        while ( rset.next() )
                        {
                            customerDirectMargin = new DirectMargin();
                            /*customerDirectMargin.setProcessstepId( rset.getInt( "PROCESSSTEP_ID" ) );
                            customerDirectMargin.setPartNumber   ( rset.getString ( "PART_NUMBER" ) );*/

                            customerDirectMargin.setCustomerId  ( rset.getInt( "CUSTOMER_ID" ) );
                            customerDirectMargin.setCustomerName( rset.getString ( "CUSTOMER_NAME" ) );

                            customerDirectMargin.setUnits        ( rset.getInt( "COOL_QTY" ) );
                            customerDirectMargin.setUnitsCost    ( rset.getFloat( "COOL_UNIT_COST" ) );
                            customerDirectMargin.setUnitsPrice   ( rset.getFloat( "PRICE" ) );
                            customerDirectMargin.setActualCost   ( rset.getFloat( "COST_CODE_COST" ) );
                            customerDirectMargin.setDirectMargin ( rset.getFloat( "DIFF_COST" ) );
                            customerDirectMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );
                            //System.out.println("                -- customerDirectMargin =  " + customerDirectMargin.toString() );
                            customerDirectMarginList.add( customerDirectMargin );
                        }
                        directMargin.setDirectMarginList( customerDirectMarginList );
                    }
                    else
                    {
                        directMargin.setDirectMarginList( null );
                    }
                    break;
                }
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }

    }


    public static void loadProductDirectMarginsByCostCode( DBTransaction dbTransaction, ArrayList directMarginList, String costCode  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        DirectMargin productDirectMargin;
        ArrayList productDirectMarginList;

        int listSize = ( directMarginList == null ) ? 0 : directMarginList.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT COST_CODE, PROCESSSTEP_ID, PART_NUMBER , SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) WHERE COST_CODE = ? GROUP BY COST_CODE, PROCESSSTEP_ID, PART_NUMBER  " );

            for ( int i = 0; i < listSize; i++ )
            {
                directMargin = ( DirectMargin ) directMarginList.get ( i );
                //System.out.println("                -- costCode obj costCode - id =  " + directMargin.getCostCode() + " costCode = " + costCode );
                if ( directMargin != null && costCode.equalsIgnoreCase( directMargin.getCostCode() ) )
                {
                    if ( directMargin.getDirectMarginList() == null || directMargin.getDirectMarginList().isEmpty()  )
                    {
                        productDirectMarginList = new ArrayList();
                        
                        pstmt.setDate	( 1, StringUtils.getSQLDate(  new Date() )  );
                        pstmt.setString	( 2, costCode );
                        rset = pstmt.executeQuery();

                        while ( rset.next() )
                        {
                            productDirectMargin = new DirectMargin();
                            productDirectMargin.setProcessstepId( rset.getInt( "PROCESSSTEP_ID" ) );
                            productDirectMargin.setPartNumber   ( rset.getString ( "PART_NUMBER" ) );
                            productDirectMargin.setUnits        ( rset.getInt( "COOL_QTY" ) );
                            productDirectMargin.setUnitsCost    ( rset.getFloat( "COOL_UNIT_COST" ) );
                            productDirectMargin.setUnitsPrice   ( rset.getFloat( "PRICE" ) );
                            productDirectMargin.setActualCost   ( rset.getFloat( "COST_CODE_COST" ) );
                            productDirectMargin.setDirectMargin ( rset.getFloat( "DIFF_COST" ) );
                            productDirectMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );

                            //System.out.println("                -- productDirectMargin =  " + productDirectMargin.toString() );
                            productDirectMarginList.add( productDirectMargin );
                        }
                        directMargin.setDirectMarginList( productDirectMarginList );
                    }
                    else
                    {
                        directMargin.setDirectMarginList( null );
                    }
                    break;
                }
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }

    }


    public static void loadSalesOrderDirectMarginsByCustomer( DBTransaction dbTransaction, ArrayList directMarginList, int customerId, int processstepId  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        DirectMargin customerDirectMargin;
        DirectMargin productDirectMargin;
        DirectMargin saleOrderDirectMargin;
        ArrayList saleOrderDirectMarginList = new ArrayList();

        int listSize = ( directMarginList == null ) ? 0 : directMarginList.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT CUSTOMER_ID, CUSTOMER_NAME, PROCESSSTEP_ID, PART_NUMBER, SALESORDER_ID, SALESORDER_NO, SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) WHERE CUSTOMER_ID = ? AND PROCESSSTEP_ID = ? GROUP BY CUSTOMER_ID, CUSTOMER_NAME, PROCESSSTEP_ID, PART_NUMBER, SALESORDER_ID, SALESORDER_NO ORDER BY SALESORDER_NO" );

            for( int i = 0; i < listSize; i++ )
            {
                customerDirectMargin = ( DirectMargin ) directMarginList.get ( i );
                //System.out.println("                        -- ByCustomer obj cust - id =  " + customerDirectMargin.getCustomerId() + " customerId = " + customerId );
                if( customerDirectMargin != null && customerDirectMargin.getCustomerId() == customerId )
                {
                    int prodListSize = ( customerDirectMargin.getDirectMarginList() == null ) ? 0 : customerDirectMargin.getDirectMarginList().size();
                    for( int j = 0; j < prodListSize; j++ )
                    {
                        productDirectMargin = ( DirectMargin ) customerDirectMargin.getDirectMarginList().get ( j );
                        //System.out.println("                        -- ByCustomer obj prod - id =  " + productDirectMargin.getProcessstepId() + " processstepId = " + processstepId );
                        if( productDirectMargin != null && productDirectMargin.getProcessstepId() == processstepId )
                        {
                            if( productDirectMargin.getDirectMarginList() == null || productDirectMargin.getDirectMarginList().isEmpty() )
                            {
                                saleOrderDirectMarginList = new ArrayList();

                                pstmt.setDate( 1, StringUtils.getSQLDate(  new Date() )  );
                                pstmt.setInt( 2, customerId );  
                                pstmt.setInt( 3, processstepId );

                                rset = pstmt.executeQuery();

                                while ( rset.next() )
                                {
                                    saleOrderDirectMargin = new DirectMargin();

                                    saleOrderDirectMargin.setSalesOrderId( rset.getInt( "SALESORDER_ID" ) );
                                    saleOrderDirectMargin.setSalesOrderNo( rset.getString ( "SALESORDER_NO" ) );
                                    saleOrderDirectMargin.setUnits      ( rset.getInt( "COOL_QTY" ) );
                                    saleOrderDirectMargin.setUnitsCost  ( rset.getFloat( "COOL_UNIT_COST" ) );
                                    saleOrderDirectMargin.setUnitsPrice ( rset.getFloat( "PRICE" ) );
                                    saleOrderDirectMargin.setActualCost   ( rset.getFloat( "COST_CODE_COST" ) );
                                    saleOrderDirectMargin.setDirectMargin( rset.getFloat( "DIFF_COST" ) );
                                    saleOrderDirectMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );

                                    //System.out.println("                -- saleOrderDirectMargin =  " + saleOrderDirectMargin.toString() );
                                    saleOrderDirectMarginList.add( saleOrderDirectMargin );
                                }
                                productDirectMargin.setDirectMarginList( saleOrderDirectMarginList );
                            }
                            else
                            {
                                productDirectMargin.setDirectMarginList( null );                                
                            }
                            break;
                        }
                    }
                    break;
                }
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }

    }


    public static void loadSalesOrderDirectMarginsByProduct( DBTransaction dbTransaction, ArrayList directMarginList, int customerId, int processstepId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        DirectMargin customerDirectMargin;
        DirectMargin productDirectMargin;
        DirectMargin saleOrderDirectMargin;
        ArrayList saleOrderDirectMarginList = new ArrayList();

        int listSize = ( directMarginList == null ) ? 0 : directMarginList.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT CUSTOMER_ID, CUSTOMER_NAME, PROCESSSTEP_ID, PART_NUMBER, SALESORDER_ID, SALESORDER_NO, SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) WHERE CUSTOMER_ID = ? AND PROCESSSTEP_ID = ? GROUP BY PROCESSSTEP_ID, PART_NUMBER, CUSTOMER_ID, CUSTOMER_NAME, SALESORDER_ID, SALESORDER_NO ORDER BY SALESORDER_NO" );

            for( int i = 0; i < listSize; i++ )
            {
                productDirectMargin = ( DirectMargin ) directMarginList.get ( i );
                //System.out.println("                        -- ByProduct obj prod - id =  " + productDirectMargin.getProcessstepId() + " processstepId = " + processstepId );
                if( productDirectMargin != null && productDirectMargin.getProcessstepId() == processstepId )
                {
                    int custListSize = ( productDirectMargin.getDirectMarginList() == null ) ? 0 : productDirectMargin.getDirectMarginList().size();
                    for( int j = 0; j < custListSize; j++ )
                    {
                        customerDirectMargin = ( DirectMargin ) productDirectMargin.getDirectMarginList().get ( j );
                        //System.out.println("                        -- ByProduct obj cust - id =  " + customerDirectMargin.getCustomerId() + " customerId = " + customerId );
                        if( customerDirectMargin != null && customerDirectMargin.getCustomerId() == customerId )
                        {
                            if( customerDirectMargin.getDirectMarginList() == null || customerDirectMargin.getDirectMarginList().isEmpty() )
                            {
                                saleOrderDirectMarginList = new ArrayList();
                                
                                pstmt.setDate( 1, StringUtils.getSQLDate(  new Date() )  );
                                pstmt.setInt( 2, customerId );
                                pstmt.setInt( 3, processstepId );

                                rset = pstmt.executeQuery();

                                while ( rset.next() )
                                {
                                    saleOrderDirectMargin = new DirectMargin();

                                    saleOrderDirectMargin.setSalesOrderId( rset.getInt( "SALESORDER_ID" ) );
                                    saleOrderDirectMargin.setSalesOrderNo( rset.getString ( "SALESORDER_NO" ) );
                                    saleOrderDirectMargin.setUnits      ( rset.getInt( "COOL_QTY" ) );
                                    saleOrderDirectMargin.setUnitsCost  ( rset.getFloat( "COOL_UNIT_COST" ) );
                                    saleOrderDirectMargin.setUnitsPrice ( rset.getFloat( "PRICE" ) );
                                    saleOrderDirectMargin.setActualCost   ( rset.getFloat( "COST_CODE_COST" ) );
                                    saleOrderDirectMargin.setDirectMargin( rset.getFloat( "DIFF_COST" ) );
                                    saleOrderDirectMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );

                                    //System.out.println("                -- saleOrderDirectMargin =  " + saleOrderDirectMargin.toString() );
                                    saleOrderDirectMarginList.add( saleOrderDirectMargin );
                                }
                                customerDirectMargin.setDirectMarginList( saleOrderDirectMarginList );
                            }
                            else
                            {
                                customerDirectMargin.setDirectMarginList( null );
                            }
                            break;
                        }
                    }
                    break;
                }
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }

    }

    public static void loadSalesOrderDirectMarginsByCostCode( DBTransaction dbTransaction, ArrayList directMarginList, String costCode, int processstepId  ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        DirectMargin costCodeDirectMargin;
        DirectMargin productDirectMargin;
        DirectMargin customerDirectMargin;
        ArrayList saleOrderDirectMarginList = new ArrayList();

        int listSize = ( directMarginList == null ) ? 0 : directMarginList.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT COST_CODE, PROCESSSTEP_ID, PART_NUMBER, CUSTOMER_ID, CUSTOMER_NAME, SUM( COOL_QTY ) AS COOL_QTY, AVG( COOL_UNIT_COST ) AS COOL_UNIT_COST, SUM( PRICE ) AS PRICE, AVG( COST_CODE_COST ) AS COST_CODE_COST, AVG( DIFF_COST ) AS DIFF_COST , AVG( DIFF_COST_PERCENTAGE ) AS DIFF_COST_PERCENTAGE FROM FN_DIRECT_MARGIN_REPORT( ? ) WHERE COST_CODE = ? AND PROCESSSTEP_ID = ? AND COST_CODE IS NOT NULL GROUP BY COST_CODE, PROCESSSTEP_ID, PART_NUMBER, CUSTOMER_ID, CUSTOMER_NAME ORDER BY CUSTOMER_NAME" );

            for( int i = 0; i < listSize; i++ )
            {
                costCodeDirectMargin = ( DirectMargin ) directMarginList.get ( i );
                //System.out.println("                        -- BycostCode obj costCode - id =  " + costCodeDirectMargin.getCostCode() + " costCode = " + costCode );
                if( costCodeDirectMargin != null && costCode.equalsIgnoreCase( costCodeDirectMargin.getCostCode() ) )
                {
                    int prodListSize = ( costCodeDirectMargin.getDirectMarginList() == null ) ? 0 : costCodeDirectMargin.getDirectMarginList().size();
                    for( int j = 0; j < prodListSize; j++ )
                    {
                        productDirectMargin = ( DirectMargin ) costCodeDirectMargin.getDirectMarginList().get ( j );
                        //System.out.println("                        -- ByCustomer obj prod - id =  " + productDirectMargin.getProcessstepId() + " processstepId = " + processstepId );
                        if( productDirectMargin != null && productDirectMargin.getProcessstepId() == processstepId )
                        {
                            if( productDirectMargin.getDirectMarginList() == null || productDirectMargin.getDirectMarginList().isEmpty() )
                            {
                                saleOrderDirectMarginList = new ArrayList();
                                
                                pstmt.setDate( 1, StringUtils.getSQLDate(  new Date() )  );
                                pstmt.setString ( 2, costCode );
                                pstmt.setInt    ( 3, processstepId );

                                rset = pstmt.executeQuery();

                                while ( rset.next() )
                                {
                                    customerDirectMargin = new DirectMargin();

                                    customerDirectMargin.setCustomerId  ( rset.getInt( "CUSTOMER_ID" ) );
                                    customerDirectMargin.setCustomerName( rset.getString ( "CUSTOMER_NAME" ) );
                                    customerDirectMargin.setUnits       ( rset.getInt( "COOL_QTY" ) );
                                    customerDirectMargin.setUnitsCost   ( rset.getFloat( "COOL_UNIT_COST" ) );
                                    customerDirectMargin.setUnitsPrice  ( rset.getFloat( "PRICE" ) );
                                    customerDirectMargin.setActualCost  ( rset.getFloat( "COST_CODE_COST" ) );
                                    customerDirectMargin.setDirectMargin( rset.getFloat( "DIFF_COST" ) );
                                    customerDirectMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );

                                    //System.out.println("                -- saleOrderDirectMargin =  " + saleOrderDirectMargin.toString() );
                                    saleOrderDirectMarginList.add( customerDirectMargin );
                                }
                                productDirectMargin.setDirectMarginList( saleOrderDirectMarginList );
                            }
                            else
                            {
                                productDirectMargin.setDirectMarginList( null );
                            }
                            break;
                        }
                    }
                    break;
                }
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }

    }

    public static ArrayList loadDirectMargins( DBTransaction dbTransaction, DirectMarginReportForm directMarginReportForm ) throws ScalarException
    {
        Connection conn         = null;
        CallableStatement cstmt = null;
        ResultSet rset          = null;
        DirectMargin directMargin;
        ArrayList directMarginList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( "{CALL SP_DIRECT_MARGIN_REPORT( ?, ?, ?, ?, ?, ?, ?, ?, ? )}" );
            cstmt.setString( 1, directMarginReportForm.getSearchCostCode() );
            cstmt.setInt ( 2, directMarginReportForm.getSearchProcessstepId() );
            cstmt.setInt ( 3, directMarginReportForm.getSearchCustomerId() );
            cstmt.setString ( 4, directMarginReportForm.getSearchStatus() );
            cstmt.setString ( 5, (directMarginReportForm.getSearchDMPercentage() == null || directMarginReportForm.getSearchDMPercentage().trim().length() == 0 ) ? null : directMarginReportForm.getSearchDMPercentage().trim() );
            cstmt.setInt ( 6, directMarginReportForm.getSearchDMPercentageOptr() );
            cstmt.setString( 7, directMarginReportForm.getSearchDateType() );
            cstmt.setString( 8, ( directMarginReportForm.getSearchFromDate() == null || directMarginReportForm.getSearchFromDate().trim().length() == 0 ) ? null : directMarginReportForm.getSearchFromDate().trim()  );
            cstmt.setString ( 9, ( directMarginReportForm.getSearchToDate() == null || directMarginReportForm.getSearchToDate().trim().length() == 0 ) ? null : directMarginReportForm.getSearchToDate().trim() );

            rset = cstmt.executeQuery();

            while ( rset.next() )
            {
                directMargin = new DirectMargin();

                directMargin.setSalesOrderId( rset.getInt( "SALESORDER_ID" ) );
                directMargin.setSalesOrderNo( rset.getString ( "SALESORDER_NO" ) );
                directMargin.setCustomerId( rset.getInt ( "CUSTOMER_ID" ) );
                directMargin.setCustomerName( rset.getString ( "CUSTOMER_NAME" ) );
                directMargin.setProcessstepId( rset.getInt( "PROCESSSTEP_ID" ) );
                directMargin.setPartNumber( rset.getString ( "PART_NUMBER" ) );
                directMargin.setCostCode    ( rset.getString ( "COST_CODE" ) );
                directMargin.setUnits       ( rset.getInt( "COOL_QTY" ) );
                directMargin.setUnitsCost   ( rset.getFloat( "COOL_UNIT_COST" ) );
                directMargin.setUnitsPrice  ( rset.getFloat( "AMOUNT" ) );
                directMargin.setActualCost  ( rset.getFloat( "COST_CODE_COST" ) );
                directMargin.setDirectMargin( rset.getFloat( "DIFF_COST" ) );
                directMargin.setDirectMarginPerctage( rset.getFloat( "DIFF_COST_PERCENTAGE" ) );

                directMarginList.add( directMargin );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Direct Margin details", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore ) {}
            }
        }
        return directMarginList;
    }
    public static Workbook getWorkbookForDirectMargin ( ArrayList directMargins, String baseGrpBy ) throws ScalarException
    {
        Workbook workbook   = null;
        Sheet sheet       = null;
        int listSize = ( directMargins == null ) ? 0 : directMargins.size();
        if ( listSize > 0 )
        {
            if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
            {
                workbook    = new XSSFWorkbook();
            }
            else
            {
                workbook    = new HSSFWorkbook();
            }
            CreationHelper ch = workbook.getCreationHelper();
            DataFormat df = workbook.createDataFormat();

            sheet       = workbook.createSheet( "Direct Margins" );
            sheet.setSelected( true );

            Font titleFont = workbook.createFont();
            titleFont.setFontHeightInPoints ( ( short ) 14 );
            titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            titleFont.setFontName( "verdana" );
            titleFont.setColor ( IndexedColors.BLACK.getIndex() );

            Font headerFont = workbook.createFont();
            headerFont.setFontHeightInPoints ( ( short ) 10 );
            headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            headerFont.setFontName( "verdana" );
            headerFont.setColor ( IndexedColors.BLACK.getIndex() );

            Font level1TextFont = workbook.createFont();
            level1TextFont.setFontHeightInPoints ( ( short ) 10 );
            level1TextFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            level1TextFont.setFontName( "verdana" );
            level1TextFont.setColor ( IndexedColors.BLUE.getIndex() );

            Font level2TextFont = workbook.createFont();
            level2TextFont.setFontHeightInPoints ( ( short ) 10 );
            level2TextFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
            level2TextFont.setFontName( "verdana" );
            level2TextFont.setColor ( IndexedColors.GREEN.getIndex() );

            Font level3TextFont = workbook.createFont();
            level3TextFont.setFontHeightInPoints ( ( short ) 10 );
            level3TextFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
            level3TextFont.setFontName( "verdana" );
            level3TextFont.setColor ( IndexedColors.BLACK.getIndex() );

            CellStyle titleStyle  = workbook.createCellStyle ( );
            titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
            titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
            titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            titleStyle.setWrapText ( true );
            titleStyle.setFont ( titleFont );

            CellStyle headerStyle  = workbook.createCellStyle ( );
            headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
            headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
            headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
            headerStyle.setWrapText ( true );
            headerStyle.setFont ( headerFont );

            CellStyle level1TextStyle  = workbook.createCellStyle ( );
            level1TextStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level1TextStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level1TextStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level1TextStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level1TextStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            level1TextStyle.setWrapText ( true );
            level1TextStyle.setFont ( level1TextFont );

            CellStyle level2TextStyle  = workbook.createCellStyle ( );
            level2TextStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level2TextStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level2TextStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level2TextStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level2TextStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            level2TextStyle.setWrapText ( true );
            level2TextStyle.setFont ( level2TextFont );

            CellStyle level3TextStyle  = workbook.createCellStyle ( );
            level3TextStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level3TextStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level3TextStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level3TextStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level3TextStyle.setAlignment ( CellStyle.ALIGN_LEFT );
            level3TextStyle.setWrapText ( true );
            level3TextStyle.setFont ( level3TextFont );

            CellStyle level1IntStyle  = workbook.createCellStyle ( );
            level1IntStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level1IntStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level1IntStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level1IntStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level1IntStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            level1IntStyle.setWrapText ( true );
            level1IntStyle.setFont ( level1TextFont );
            level1IntStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle level2IntStyle  = workbook.createCellStyle ( );
            level2IntStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level2IntStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level2IntStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level2IntStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level2IntStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            level2IntStyle.setWrapText ( true );
            level2IntStyle.setFont ( level2TextFont );
            level2IntStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle level3IntStyle  = workbook.createCellStyle ( );
            level3IntStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level3IntStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level3IntStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level3IntStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level3IntStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            level3IntStyle.setWrapText ( true );
            level3IntStyle.setFont ( level3TextFont );
            level3IntStyle.setDataFormat( df.getFormat( "#,##0" ) );

            CellStyle level1FloatStyle  = workbook.createCellStyle ( );
            level1FloatStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level1FloatStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level1FloatStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level1FloatStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level1FloatStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            level1FloatStyle.setWrapText ( true );
            level1FloatStyle.setFont ( level1TextFont );
            level1FloatStyle.setDataFormat( df.getFormat( "#,##0.00" ) );

            CellStyle level2FloatStyle  = workbook.createCellStyle ( );
            level2FloatStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level2FloatStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level2FloatStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level2FloatStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level2FloatStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            level2FloatStyle.setWrapText ( true );
            level2FloatStyle.setFont ( level2TextFont );
            level2FloatStyle.setDataFormat( df.getFormat( "#,##0.00" ) );

            CellStyle level3FloatStyle  = workbook.createCellStyle ( );
            level3FloatStyle.setBorderBottom ( CellStyle.BORDER_THIN );
            level3FloatStyle.setBorderTop ( CellStyle.BORDER_THIN );
            level3FloatStyle.setBorderLeft ( CellStyle.BORDER_THIN );
            level3FloatStyle.setBorderRight ( CellStyle.BORDER_THIN );
            level3FloatStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
            level3FloatStyle.setWrapText ( true );
            level3FloatStyle.setFont ( level3TextFont );
            level3FloatStyle.setDataFormat( df.getFormat( "#,##0.00" ) );

            //SETTING HEADINGS
            String headers[] = null;
            if ( "customer".equalsIgnoreCase( baseGrpBy ) )
            {
                headers = new String[]{"   Customer   ", "    Product    ", "Sales Order", "Price", "Direct Margin", "Direct Margin %", "Units", "Units Cost", "Actual Cost"};
            }
            else if ( "product".equalsIgnoreCase( baseGrpBy ) )
            {
                headers = new String[]{ "    Product    ", "   Customer   ", "Sales Order", "Price", "Direct Margin", "Direct Margin %", "Units", "Units Cost", "Actual Cost"};
            }
            else if ( "costCode".equalsIgnoreCase( baseGrpBy ) )
            {
                headers = new String[]{ "Cost Code", "    Product    ", "   Customer   ", "Price", "Direct Margin", "Direct Margin %", "Units", "Units Cost", "Actual Cost"};
            }

            Row row = sheet.createRow( 0 );
            Cell cell = mergeCells ( sheet, 0, 0, 0, (headers.length-1), titleStyle );
            setCellValue ( cell, ch.createRichTextString("Direct Margin Report"), titleStyle );

            row = sheet.createRow ( 2 );
            for ( int i = 0; i < headers.length; i++ )
            {
                cell    = row.createCell( i );
                setCellValue ( cell, ch.createRichTextString(headers[i]), headerStyle );
                int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
                sheet.setColumnWidth( i, (short) ( ( ( width + 2 ) * 256 ) ) );
            }

            int nextRowNumber   = 3;
            int nextColumnIdx = 0;
            for ( int i = 0; i < listSize; i++ )
            {
                DirectMargin directMargin    =  ( DirectMargin )directMargins.get ( i );
                row = sheet.createRow( nextRowNumber++ );
                nextColumnIdx = 0;
                CellStyle textStyle = level1TextStyle;
                CellStyle intStyle = level1IntStyle;
                CellStyle floatStyle = level1FloatStyle;
                String col1 = "";
                String col2 = "";
                String col3 = "";
                if ( directMargin.getLevel() == 1 )
                {
                    textStyle = level1TextStyle;
                    intStyle = level1IntStyle;
                    floatStyle = level1FloatStyle;
                    if ( "customer".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col1 = directMargin.getCustomerName();
                    }
                    else if ( "product".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col1 = directMargin.getPartNumber();
                    }
                    else if ( "costCode".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col1 = directMargin.getCostCode();
                    }
                }
                else if ( directMargin.getLevel() == 2 )
                {
                    textStyle = level2TextStyle;
                    intStyle = level2IntStyle;
                    floatStyle = level2FloatStyle;
                    if ( "customer".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col2 = directMargin.getPartNumber();
                    }
                    else if ( "product".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col2 = directMargin.getCustomerName();
                    }
                    else if ( "costCode".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col2 = directMargin.getPartNumber();
                    }
                }
                else if ( directMargin.getLevel() == 3 )
                {
                    textStyle = level3TextStyle;
                    intStyle = level3IntStyle;
                    floatStyle = level3FloatStyle;
                    if ( "customer".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col3 = directMargin.getSalesOrderNo();
                    }
                    else if ( "product".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col3 = directMargin.getSalesOrderNo();
                    }
                    else if ( "costCode".equalsIgnoreCase( baseGrpBy ) )
                    {
                        col3 = directMargin.getCustomerName();
                    }
                }
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(col1), textStyle  );
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(col2), textStyle  );
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(col3), textStyle  );

                setCellValue( row, nextColumnIdx++, directMargin.getUnitsPrice(), floatStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, directMargin.getDirectMargin(), floatStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, directMargin.getDirectMarginPerctage(), floatStyle, Cell.CELL_TYPE_NUMERIC  );

                setCellValue( row, nextColumnIdx++, directMargin.getUnits(), intStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, directMargin.getUnitsCost(), floatStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, directMargin.getActualCost(), floatStyle, Cell.CELL_TYPE_NUMERIC  );
            }
        }
        return workbook;
    }
    /**
     * merges cells and sets each cell with the given cellStyle.
     * @param sheet
     * sheet object in which the cells has to be merged.
     * @param startRow
     * start row Number to merge from.<br>
     * starts from 0.
     * @param startCell
     * start cell number in the start row to merge from.<br>
     * starts from 0.
     * @param endRow
     * end row Number<br>
     * starts from 0.
     * @param endCell
     * end cell number<br>
     * starts from 0.
     * @param endCell
     * @return
     * returns cell object of the startRow, startCell.
     */
    private static Cell mergeCells ( Sheet sheet, int startRow, int startCell, int endRow, int endCell, CellStyle cellStyle )
    {
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell, endCell ) );
        Cell cell = null;
        for ( int rowIdx = startRow; rowIdx <= endRow; rowIdx++ )
        {
            for ( int cellIdx = startCell; cellIdx <= endCell; cellIdx++ )
            {
                cell = getCellAt ( sheet.getRow ( rowIdx ), cellIdx );
                cell.setCellStyle ( cellStyle );
            }
        }
        return getCellAt ( sheet.getRow( startRow), startCell );
    }
    /**
     * Returns the cell object at the specified cell in the row.<br>
     * It creats cell if doesn't exists.
     * @param row
     * row object from which cell object has to be picked
     * @param cellNumber
     * starts from 0.
     * @return
     */
    private static Cell getCellAt ( Row row, int cellNumber )
    {
        Cell cell = null;
        cell = row.getCell( cellNumber );
        if ( cell == null )
        {
            cell = row.createCell ( cellNumber );
        }
        return cell;
    }
    private static void setCellValue ( Cell cell, RichTextString value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }

    private static void setCellValue ( Row row, int cellNo, RichTextString value, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }
    private static void setCellValue ( Row row, int cellNo, int value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }
    private static void setCellValue ( Row row, int cellNo, double value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }
}
